package com.toncen.samepms.fisher.query;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.josql.Query;
import org.josql.QueryResults;

import com.sky.common.string.StringUtils;
import com.sky.fisher.db.AbstractConnectionProvider;
import com.sky.fisher.generate.AbstractColumnMetaData;
import com.sky.fisher.generate.SQLColumnMetaData;
import com.sky.fisher.model.CheckboxColumnModel;
import com.sky.fisher.model.CommonColumnModel;
import com.sky.fisher.model.GridModel;
import com.sky.fisher.model.PositionColumnModel;
import com.sky.fisher.query.AbstractQuery;
import com.sky.fisher.query.PageResult;
import com.sky.fisher.service.FisherService;

/**
 * Memory查询器
 * 
 * @author tch
 * 
 */
public class MemoryQuery extends AbstractQuery {
	public MemoryQuery(AbstractConnectionProvider connectionProvider) {
		super(connectionProvider);
	}

	/**
	 * @param parameter
	 * @param grid
	 * @return
	 * @throws Exception
	 */
	protected List<String> getColumnSelect(GridModel grid) throws Exception {
		List<String> columnSelect = new ArrayList<String>();
		String name = FisherService.SQL_ID_NAME;
		columnSelect.add(name);
		for (Iterator iterator = grid.getColumnList().iterator(); iterator.hasNext();) {
			CommonColumnModel column = (CommonColumnModel) iterator.next();
			if (!(column instanceof PositionColumnModel)// 非索引列
					&& !(column instanceof CheckboxColumnModel)// 非选择列
					&& "true".equalsIgnoreCase(column.getVisual())) {// 可见的
				name = column.getName();
				columnSelect.add(name);
			}
		}
		return columnSelect;
	}

	/**
	 * 执行分页查询
	 * 
	 * @param sql
	 * @param page
	 * @param pagesize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public PageResult executeQuery(String sql, int page, int pagesize) throws Exception {
		PageResult pageresult = new PageResult(page, pagesize);
		if (StringUtils.isEmpty(sql))
			return pageresult;
		logger.debug("执行查询：\n" + sql);
		Collection data = (Collection) provider.getConnection();
		List results = execute(data, sql);
		int allRowsCount = results.size();
		pageresult.setTotal(allRowsCount);
		int maxPage = (allRowsCount / pagesize) + (allRowsCount % pagesize == 0 ? 0 : 1);
		if (page > maxPage)
			page = maxPage; // 当前页超过最大页，则置为首页
		int beginNum = (page - 1) * pagesize;
		if (beginNum < 0) {
			beginNum = 0;
		}
		int endNum = page * pagesize;
		if (endNum > allRowsCount)
			endNum = allRowsCount;
		List<String> columnSelect = getColumnSelect(provider.getGrid());
		HashMap<String, String> rowMap = null;
		Object value = null;
		List<Object> row;
		for (int i = beginNum; i < endNum; i++) {
			rowMap = new HashMap<String, String>(5);
			row = (List) results.get(i);
			for (int j = 0; j < row.size(); j++) {
				value = row.get(j);
				if (value instanceof Date) {
					value = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format((Date) value);
				}
				rowMap.put(columnSelect.get(j).toUpperCase(), StringUtils.isEmpty(value) ? "" : value.toString());
			}
			pageresult.getResult().add(rowMap);
		}
		logger.debug("查询结果：\n" + pageresult.getResult());
		return pageresult;
	}

	/**
	 * 执行查询
	 * 
	 * @param con
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, String>> executeQuery(String sql) throws Exception {
		List<Map<String, String>> resultList = new ArrayList<Map<String, String>>(5);
		Collection data = (Collection) provider.getConnection();
		List results = execute(data, sql);
		Map<String, String> rowMap = null;
		Object value = null;
		for (Object obj : results) {
			rowMap = new HashMap<String, String>(5);
			for (Field field : obj.getClass().getDeclaredFields()) {
				field.setAccessible(true);
				value = field.get(obj);
				if (value instanceof Date) {
					value = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format((Date) value);
				}
				rowMap.put(field.getName().toUpperCase(), StringUtils.isEmpty(value) ? "" : value.toString());
			}
			resultList.add(rowMap);
		}
		logger.debug("查询结果：\n" + resultList);
		return resultList;
	}

	/**
	 * 获取总记录数目
	 * 
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public int getTotalNumber(String sql) throws Exception {
		logger.debug("解析查询：\n" + sql);
		Collection data = (Collection) provider.getConnection();
		List results = execute(data, sql);
		return results.size();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.sky.fisher.db.AbstractQuery#getResultSetMetaDataMap(java.lang.String)
	 */
	public List<AbstractColumnMetaData> getColumnMetaData(String sql) throws Exception {
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			logger.debug("执行查询：\n" + sql);
			con = (Connection) provider.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			List<AbstractColumnMetaData> columnResultSetMetaData = new ArrayList<AbstractColumnMetaData>(5);
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				columnResultSetMetaData.add(new SQLColumnMetaData(rsmd.getTableName(i), rsmd.getColumnName(i), rsmd.getColumnLabel(i), rsmd
						.getColumnTypeName(i), rsmd.getColumnClassName(i)));
			}
			logger.debug("查询结果：\n" + columnResultSetMetaData);
			return columnResultSetMetaData;
		} catch (Exception e) {
			throw e;
		} finally {
			provider.closeConnection(con);
		}
	}

	/**
	 * @param data
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	private List execute(Collection data, String sql) throws Exception {
		Query query = new Query();
		query.parse(sql);
		QueryResults results = query.execute(data);
		return results.getResults();
	}
}
